﻿using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Options;
using Oracle.ManagedDataAccess.Client;
using System.Data;
using System.Reflection;
using System.Text;
using WMS.FC.Domain.DomainObject.System;
using WMS.FC.Domain.PersistenceObject;
using WMS.FC.Domain.Repository;
using WMS.FC.Infrastructure.Common;

namespace WMS.FC.Infrastructure.Repository
{
    public class SplitTableRepository : SystemRepository, ISplitTableRepository
    {
        protected readonly Dictionary<string, SplitTableEntityInfo> SplitTableEntityInfo = new Dictionary<string, SplitTableEntityInfo>();

        public SplitTableRepository(WmsDbContext dbContext, IOptions<WmsSettings> options) : base(dbContext, options)
        {
        }

        public async Task InitializeTable<TSource>() where TSource : HistoryPOBase
        {
            var entityInfo = await GetSplitTableEntityInfo<TSource>();
            var dateTime = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1);
            if (!entityInfo.SplitTableNames.Any((item) => item.Key.Year == dateTime.Year && item.Key.Month == dateTime.Month))
                await CreateSplitTable(entityInfo, dateTime);

            dateTime = dateTime.AddMonths(1);
            if (!entityInfo.SplitTableNames.Any((item) => item.Key.Year == dateTime.Year && item.Key.Month == dateTime.Month))
                await CreateSplitTable(entityInfo, dateTime);
        }

        public async Task<SplitTableEntityInfo> GetSplitTableEntityInfo<TSource>() where TSource : HistoryPOBase
        {
            var entityType = typeof(TSource);
            return await GetSplitTableEntityInfo(entityType);
        }

        public async Task<SplitTableEntityInfo> GetSplitTableEntityInfo(Type entityType)
        {
            if (SplitTableEntityInfo.ContainsKey(entityType.Name))
            {
                return SplitTableEntityInfo[entityType.Name];
            }
            else
            {
                var tableInfo = entityType.GetCustomAttribute<SplitTableAttribute>();
                if (tableInfo == null)
                    throw new Exception($"类名:({entityType.Name}),没有设置分表特性,不能使用");

                var entityInfo = new SplitTableEntityInfo(tableInfo);
                var properties = entityType.GetProperties();
                foreach (var property in properties)
                {
                    var columnInfo = property.GetCustomAttribute<SplitTableColumnAttribute>();
                    if (columnInfo != null)
                    {
                        if (columnInfo.IsPrimaryKey)
                        {
                            entityInfo.Key = property.Name;
                            entityInfo.KeyColumnInfo = columnInfo;
                        }
                        else
                        {
                            entityInfo.ColumnList.Add(property.Name, columnInfo);
                        }

                        entityInfo.PropertyList.Add(property.Name, property);
                    }
                }

                entityInfo.ColumnList.Add(entityInfo.Key, entityInfo.KeyColumnInfo);
                entityInfo.IndexList = entityType.GetCustomAttributes<SplitTableIndexAttribute>().ToList();
                entityInfo.SplitTableNames = await GetSplitTableNames(entityInfo.SplitTable.TableName);
                SplitTableEntityInfo.Add(entityType.Name, entityInfo);
                return entityInfo;
            }
        }

        protected async Task<Dictionary<DateTime, string>> GetSplitTableNames(string tableName)
        {
            var sql = $"SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME LIKE '{tableName}%' ORDER BY TABLE_NAME";
            var table = await ExecuteQuery(sql);
            var result = new Dictionary<DateTime, string>();
            foreach (var row in table.AsEnumerable())
            {
                var splitTableName = row["TABLE_NAME"].ToString();
                if (string.IsNullOrWhiteSpace(splitTableName))
                    throw new Exception("获取分表异常");

                var year = splitTableName.Replace(tableName, "").Substring(0, 4);
                var month = splitTableName.Replace(tableName, "").Substring(4, 2);

                result.Add(Convert.ToDateTime($"{year}-{month}-01"), splitTableName);
            }

            return result;
        }

        protected async Task<string> CreateSplitTable(SplitTableEntityInfo entityInfo, DateTime dateTime)
        {
            var builder = new StringBuilder();
            var tableName = $"{entityInfo.SplitTable.TableName}{dateTime:yyyy}{dateTime:MM}";

            builder.Append($"CREATE TABLE {tableName} (\r\n");
            builder.Append($"\"{entityInfo.KeyColumnInfo.ColumnName}\" {entityInfo.KeyColumnInfo.ColumnDataType} GENERATED BY DEFAULT ON NULL AS IDENTITY NOT NULL");
            foreach (var column in entityInfo.ColumnList)
            {
                if (column.Key == entityInfo.Key) continue;
                builder.Append($",\r\n\"{column.Value.ColumnName}\" {column.Value.ColumnDataType} {(string.IsNullOrWhiteSpace(column.Value.DefaultValue) ? "" : $"DEFAULT {column.Value.DefaultValue}")} {(column.Value.IsNullable ? "" : "NOT NULL")}");
            }

            builder.Append($",\r\nCONSTRAINT \"PK_{tableName}\" PRIMARY KEY (\"{entityInfo.KeyColumnInfo.ColumnName}\")) NOLOGGING");
            await dbContext.Database.ExecuteSqlRawAsync(builder.ToString());

            foreach (var column in entityInfo.ColumnList)
            {
                await dbContext.Database.ExecuteSqlRawAsync($"COMMENT ON COLUMN \"{tableName}\".\"{column.Value.ColumnName}\" is '{column.Value.ColumnDescription}'");
            }

            foreach (var index in entityInfo.IndexList)
            {
                var columnNameList = index.PropertyNameList.Select(propertyName => entityInfo.ColumnList[propertyName].ColumnName);
                await dbContext.Database.ExecuteSqlRawAsync($"CREATE {(index.IsUnique ? "UNIQUE" : "")} INDEX \"{$"IDX_{tableName}_{index.IndexName}"}\" ON \"{tableName}\" (\"{string.Join("\",\"", columnNameList)}\") NOLOGGING ONLINE");
            }

            entityInfo.SplitTableNames.Add(dateTime, tableName);
            return tableName;
        }

        public async Task<int> InsertSplitTable<TSource>(TSource entity, UserInfo userInfo, DateTime? dateTime = null) where TSource : HistoryPOBase
        {
            var dateTimeInsert = dateTime.HasValue ? dateTime.Value : DateTime.Now;
            var entityInfo = await GetSplitTableEntityInfo<TSource>();
            var tableName = string.Empty;
            if (entityInfo.SplitTableNames.Any((item) => item.Key.Year == dateTimeInsert.Year && item.Key.Month == dateTimeInsert.Month))
            {
                tableName = entityInfo.SplitTableNames.First((item) => item.Key.Year == dateTimeInsert.Year && item.Key.Month == dateTimeInsert.Month).Value;
            }
            else
            {
                tableName = await CreateSplitTable(entityInfo, dateTimeInsert);
            }

            if (entity.Id == 0)
                entity.Id = SnowflakeIdHelper.Instance.NextId();
            entity.CreatedBy = userInfo.UserName;
            entity.CreateDate = dateTimeInsert;
            entity.ModifyIp = userInfo.UserIp;

            var columnNameList = entityInfo.ColumnList.Values.Select(column => column.ColumnName);
            var sql = $"INSERT INTO {tableName} ({string.Join(",", columnNameList.Select(columnName => $"\"{columnName}\""))}) VALUES ({string.Join(",", columnNameList.Select(columnName => $":{columnName}"))})";

            var sqlParameterList = new List<OracleParameter>
            {
                new OracleParameter("WAREHOUSE_CODE", options.Value.WarehouseCode),
                new OracleParameter("LINE_CODE", options.Value.LineCode),
            };

            foreach (var column in entityInfo.ColumnList)
            {
                sqlParameterList.Add(new OracleParameter(column.Value.ColumnName, GetColumnValue(entityInfo.PropertyList[column.Key], entity)));
            }

            return await dbContext.Database.ExecuteSqlRawAsync(sql, sqlParameterList);
        }

        public async Task<int> InsertSplitTable<TSource>(List<TSource> entityList, UserInfo userInfo) where TSource : HistoryPOBase
        {
            var entityGroups = entityList.GroupBy(entity => entity.CreateDate.ToString("yyyy-MM-01"));
            var entityInfo = await GetSplitTableEntityInfo<TSource>();
            var tableName = string.Empty;
            var count = 0;
            var dateTime = DateTime.Now;
            entityList.ForEach(entity =>
            {
                if (entity.Id == 0)
                    entity.Id = SnowflakeIdHelper.Instance.NextId();

                if (string.IsNullOrWhiteSpace(entity.WarehouseCode))
                    entity.WarehouseCode = options.Value.WarehouseCode;

                if (string.IsNullOrWhiteSpace(entity.LineCode))
                    entity.LineCode = options.Value.LineCode;

                if (string.IsNullOrWhiteSpace(entity.CreatedBy))
                    entity.CreatedBy = userInfo.UserName;

                if (entity.CreateDate == new DateTime(2000, 1, 1))
                    entity.CreateDate = dateTime;

                if (string.IsNullOrWhiteSpace(entity.ModifyIp))
                    entity.ModifyIp = userInfo.UserIp;
            });

            foreach (var group in entityGroups)
            {
                var dateTimeInsert = Convert.ToDateTime(group.Key);
                if (entityInfo.SplitTableNames.Any((item) => item.Key.Year == dateTimeInsert.Year && item.Key.Month == dateTimeInsert.Month))
                {
                    tableName = entityInfo.SplitTableNames.First((item) => item.Key.Year == dateTimeInsert.Year && item.Key.Month == dateTimeInsert.Month).Value;
                }
                else
                {
                    tableName = await CreateSplitTable(entityInfo, dateTimeInsert);
                }

                var groupCount = group.Count();
                var dataList = group.ToList();
                if (groupCount < 500)
                {
                    count += await InsertSplitTable(dataList, entityInfo, tableName, userInfo);
                }
                else
                {
                    for (var i = 0; i < groupCount; i += 500)
                    {
                        var offset = 500;
                        if (i + offset > groupCount)
                            offset = groupCount % 500;
                        var tempList = dataList.GetRange(i, offset);
                        count += await InsertSplitTable(tempList, entityInfo, tableName, userInfo);
                    }
                }
            }

            return count;
        }

        protected async Task<int> InsertSplitTable<TSource>(List<TSource> entityList, SplitTableEntityInfo entityInfo, string tableName, UserInfo userInfo) where TSource : HistoryPOBase
        {
            var builder = new StringBuilder();
            var columnNameList = entityInfo.ColumnList.Values.Select(column => column.ColumnName);
            builder.AppendLine($"INSERT ALL ");
            foreach (var entity in entityList)
            {
                var valueList = entityInfo.ColumnList.Select(item => GetColumnValueString(entityInfo.PropertyList[item.Key], entity)).ToList();
                builder.AppendLine($"INTO {tableName} ({string.Join(",", columnNameList.Select(columnName => $"\"{columnName}\""))}) VALUES ({string.Join(",", valueList)})");
            }

            builder.AppendLine("SELECT 1 FROM DUAL;");
            return await dbContext.Database.ExecuteSqlRawAsync(builder.ToString());
        }

        public async Task<DataTable?> ExecuteQuery<TSource>(DateTime startTime, DateTime endTime, Dictionary<string, object>? parameters = null, Pagination? pagination = null, Sorter? sorter = null) where TSource : HistoryPOBase
        {
            var entityInfo = await GetSplitTableEntityInfo<TSource>();
            var tableNameList = entityInfo.SplitTableNames.Where(item => (item.Key.Year == startTime.Year && item.Key.Month == startTime.Month) || (item.Key.Year == endTime.Year && item.Key.Month == endTime.Month) || (startTime < item.Key && item.Key < endTime)).Select(item => item.Value).ToList();
            var sqlList = new List<string>();
            var sql = string.Empty;
            var whereExpression = new StringBuilder();
            var sqlParameterList = new List<OracleParameter>()
            {
                new OracleParameter("CREATE_DATE1",startTime),
                new OracleParameter("CREATE_DATE2",endTime),
            };

            if (parameters != null)
            {
                foreach (var parameter in parameters)
                {
                    whereExpression.Append($"AND {parameter.Key}=:{parameter.Key}");
                    sqlParameterList.Add(new OracleParameter(parameter.Key, parameter.Value));
                }
            }

            foreach (var tableName in tableNameList)
            {
                sqlList.Add($"SELECT * FROM {tableName} WHERE CREATE_DATE>=:CREATE_DATE1 AND CREATE_DATE<=:CREATE_DATE2 {whereExpression}");
            }

            sql = GenerateSql(string.Join(" UNION ALL ", sqlList), pagination, sorter);
            return await ExecuteQuery(sql, sqlParameterList);
        }

        protected string GenerateSql(string sql, Pagination? pagination = null, Sorter? sorter = null)
        {
            var result = sql;

            if (sorter != null)
            {
                result = $"SELECT * FROM ({result}) ORDER BY {sorter.SortField} {(sorter.SortOrder == "descending" ? "DESC" : "ASC")}";
            }

            if (pagination != null)
            {
                result = $"SELECT * FROM (SELECT TEMP.*,ROWNUM AS RN FROM ({result}) TEMP WHERE ROWNUM <= {pagination.PageNumber * pagination.PageSize}) TEMP1 WHERE TEMP1.RN > {(pagination.PageNumber - 1) * pagination.PageSize}";
            }

            if (pagination == null && sorter == null)
            {
                result = $"SELECT * FROM ({sql})";
            }

            return result;
        }

        protected object? GetColumnValue<TSource>(PropertyInfo propertyInfo, TSource entity) where TSource : HistoryPOBase
        {
            var tempValue = propertyInfo.GetValue(entity);
            switch (propertyInfo.PropertyType.Name)
            {
                case "Boolean":
                    return (bool)tempValue! ? "1" : "0";
                default:
                    return tempValue;
            }
        }

        protected string GetColumnValueString<TSource>(PropertyInfo propertyInfo, TSource entity) where TSource : HistoryPOBase
        {
            var tempValue = propertyInfo.GetValue(entity);
            switch (propertyInfo.PropertyType.Name)
            {
                case "Int16":
                    return tempValue != null ? tempValue.ToString()! : "null";
                case "Int32":
                    return tempValue != null ? tempValue.ToString()! : "null";
                case "Int64":
                    return tempValue != null ? tempValue.ToString()! : "null";
                case "Decimal":
                    return tempValue != null ? tempValue.ToString()! : "null";
                case "Boolean":
                    return tempValue != null ? ((bool)tempValue ? "1" : "0") : "null";
                case "Byte":
                    return tempValue != null ? tempValue.ToString()! : "null";
                case "String":
                    return tempValue != null ? $"'{tempValue}'" : "null";
                case "DateTime":
                    return tempValue != null ? $"TO_DATE('{(DateTime)tempValue:yyyy-MM-dd HH:mm:ss}','yyyy-MM-dd HH24:mi:ss')" : "null";
                default:
                    return tempValue != null ? tempValue.ToString()! : "null";
            }
        }
    }
}
